package jdbc;
import json.studentDTO;
import java.sql.*;
import javax.json.bind.Jsonb;
import javax.json.bind.JsonbBuilder;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.math.BigDecimal;
import java.time.LocalDate;
import java.util.stream.Collectors;
/**
 * @author Sai
 * Created by Sai on 2019-03-25.
 * <p>
 * <p>
 * <p>
 * JDBC编程步骤：
 * 0.加载数据库驱动程序
 * 1.连接到数据库
 * 2.创建SQL或MySQL语句
 * 3.在数据库中执行SQL或MySQL查询
 * 4.查看和修改记录
 * <p>
 * H2官方教程：
 * http://www.h2database.com/html/tutorial.html
 */
@WebServlet(urlPatterns = {"/json"})
public class DBUtil extends HttpServlet {
    //  如果数据库不存在，会在用户目录创建一个数据库文件，缺省用户是sa，密码是空字符串
    private static final String URL = "jdbc:h2:~/test;MODE=MYSQL;DB_CLOSE_DELAY=-1";
    //  本地连接的内存模式，程序模式时，应用程序结束后，数据库就被清除。
    private static final String URL_MEM = "jdbc:h2:mem:test;MODE=MYSQL;DB_CLOSE_DELAY=-1";

    private static final String URL_TCP = "jdbc:h2:tcp://localhost/~/test1;MODE=MYSQL;DB_CLOSE_DELAY=-1";
    // 内存模式
    private static final String URL_TCP_MEM = "jdbc:h2:tcp://localhost/mem:test1;MODE=MYSQL;DB_CLOSE_DELAY=-1";

    private static final String DRIVER = "org.h2.Driver";

    private static final String NAME = "sa";
    private static final String PASSWORD = "sa";

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {

        Jsonb jsonb = JsonbBuilder.create();
        String postJsonString = new BufferedReader(new InputStreamReader(req.getInputStream()))
                .lines().collect(Collectors.joining(System.lineSeparator()));

        // 利用jsonb把json字符串转换为对象
        studentDTO studentdto = jsonb.fromJson(postJsonString, studentDTO.class);
        int studentid = studentdto.getStudentid();
        String sex = studentdto.getSex();
        String name = studentdto.getName();
        int age = studentdto.getAge();
        String where = studentdto.getWhere();
        if(studentid != 0 && sex != null && name != null && age != 0 && where != null){
            try (Connection conn = DriverManager.getConnection(URL, NAME, PASSWORD)) {
                Statement stmt = conn.createStatement();

                // 创建一个表
                // 如果表不存在就建立这个表，那么可以直接用 create table if not exists tablename 这样的指令来建立，不需要先去查询表是否存在。
                // 从模板表创建表：create table if not exists like old_table_name;
                stmt.executeUpdate(
                        "CREATE TABLE IF NOT EXISTS `student` (" +
                                "  `studentid` int(10) NOT NULL," +
                                "  `sex` varchar(100) NOT NULL," +
                                "  `name` varchar(100) NOT NULL," +
                                "  `age` int(11) NOT NULL," +
                                "  `where` varchar(100) NOT NULL," +
                                "  PRIMARY KEY (`studentid`)" +
                                ");"
                );
                // 插入数据记录
                PreparedStatement ps = conn.prepareStatement("insert into `student` (`studentid`,`sex`,`name`,`age`,`where`) values (?,?,?,?,?)");
                ps.setInt(1,studentid);
                ps.setString(2,sex);
                ps.setString(3,name);
                ps.setInt(4,age);
                ps.setString(5,where);
                ps.execute();
                ResultSet rs = stmt.executeQuery("select * from student");
                // 遍历每行记录
                while (rs.next()) {
                    //循环打印数据
                    System.out.println(rs.getInt("studentid") + "," + rs.getString("sex") +  "," + rs.getString("name") + "," + rs.getInt("age") + "," +  rs.getString("where"));
                }

            } catch (SQLException e) {
                e.printStackTrace();
            }
            // 把获取的字符串原样返回
            String jsonStudent = jsonb.toJson(studentdto);

            resp.setContentType("application/json");
            resp.setCharacterEncoding("UTF-8");

            resp.getWriter().println(jsonStudent);
            resp.getWriter().println("{\"code\":0,\"msg\":\"操作成功\"}");
        }else {
            resp.getWriter().println("{\"code\":-1,\"msg\":\"操作失败\"}");
        }
    }
}
